﻿using Mono.Data.Sqlite;
using System.Data;
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using System;
using LitJson;

public interface IDataBase 
{
    void Init();
    void Close();
    Player GetUserData(int id);

    void SavePlayerData(Player save);
}

public class SQliteMgr:BaseManager<SQliteMgr>,IDataBase
{
    private SqliteConnection conn;
    private SqliteCommand cmd;

    const string PlayerTableName = "PlayerData";
    public void Init()
    {
        //创建数据库
        conn = new SqliteConnection("Data Source=./ServerDB.db");
        conn.Open();
        //连接数据库
        cmd = conn.CreateCommand();
        _initTables();
    }
    //初始化数据库
    private void _initTables()
    {
        string sqlStr = "create table if not exists{0}(id integer not null primary key autoincrement unique,Data Text);".ToUpper();
        Debug.Log(sqlStr);
        var sql = string.Format("create table if not exists {0}(id integer not null primary key autoincrement unique,Data Text);".ToUpper(),PlayerTableName);
        
        _excuteNoQuery(sql);
    }

    //将数据添加到数据库中
    private void _excuteNoQuery(string cmdStr,SqliteParameter[] parameters=null)
    {
        cmd.CommandText = cmdStr;
        Debug.Log("<color=red>[write db]</color>" + cmdStr);
        if (parameters != null)
        {
            cmd.Parameters.AddRange(parameters);

        }
        cmd.ExecuteNonQuery();
        cmd.Parameters.Clear();
    }
    //从数据库提取数据
    private DataTable _excuteSelectTable(string sql, SqliteParameter[] parameters = null)
    {
        cmd.CommandText = sql;
        if (parameters != null)
        {
            cmd.Parameters.AddRange(parameters);
        }
        SqliteDataAdapter adapter = new SqliteDataAdapter(cmd);
        DataTable data = new DataTable();
        adapter.Fill(data);
        return data;
    }


    //获取玩家数据
    public Player GetUserData(int id)
    {
        //提取数据库中对应的id玩家的数据
        var sql = string.Format("select Data from {0} where id ={1}", PlayerTableName, id);
        //解析数据库该对应的id玩家的数据
        var dataTable = _excuteSelectTable(sql);
        if (dataTable.Rows.Count <= 0)
            return null;
        //提取玩家的对象并返回出去
        string json = dataTable.Rows[0][0].ToString();
        return JsonMapper.ToObject<Player>(json);

    }
    //保存玩家数据
    public void SavePlayerData(Player playerData)
    {
        //将数据封装成Json格式，PlayerTableName为表名，保存playerData的id和player这个对象
        var sql = string.Format("INSERT OR REPLACE INTO {0} VALUES({1},'{2}')", PlayerTableName, playerData.ThisID, JsonMapper.ToJson(playerData));
        _excuteNoQuery(sql);
         
    }
    //关闭数据库
    public void Close()
    {
        conn.Close();
    }
}
    

